Merge or split cells or data

Text spread and centered over multiple cells

Warning Microsoft Excel places only the upper-leftmost data in the selected range into the resulting merged cell. If there is data in other cells, the data is deleted.

Copy the data you want into the upper-leftmost cell within the range.
Select the cells you want to merge.
To merge cells in a row or column and center the cell contents, click Merge and Center on the Formatting toolbar.

Show Tip

You can change the text alignment in the merged cell by clicking Align Left Button image , Center , or Align Right on the Formatting toolbar. To make other changes to the text alignment, including the vertical alignment, use the options on the Alignment tab (Cells command, Format menu).

Select the merged cell.
When cells have been combined, Merge and Center on the Formatting toolbar is selected.
Click Merge and Center on the Formatting toolbar.

Select the range of cells that contains the text values. The range can be any number of rows tall, but no more than one column wide.
Note There must be one or more blank columns to the right of the selected column or the data to the right of the selected column will be overwritten.
On the Data menu, click Text to Columns.
Follow the instructions in the Convert Text to Columns Wizard to specify how you want to divide the text into columns.

Use the ampersand (&) operator or the CONCATENATE function to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

Show How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1

2
3

A	B
First Name	Last Name
Nancy	Davolio
Andrew	Fuller
Formula	Description (Result)
=A2&" "&B2	Combines the names above, separated by a space (Nancy Davolio)
=B3&", "&A3	Combines the names above, separated by a comma (Fuller, Andrew)
=CONCATENATE(A2," ",B2)	Combines the names above, separated by a space (Nancy Davolio)

Note The formula inserts a space between the first and last names by using a space enclosed within quotation marks. Use quotation marks to include any literal text— text that does not change— in the result.

Function details

CONCATENATE